﻿namespace AnyOffice.DAL
{
    using AnyOffice.Components;
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class AttendHolidayDao
    {
        public bool Delete(SqlConnection conn, int holidayId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Attend_Holiday] ");
            builder.Append("WHERE ");
            builder.Append("\t[HolidayId] = @HolidayId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@HolidayId", SqlDbType.Int) };
            commandParameters[0].Value = holidayId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool DeleteAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Attend_Holiday] ");
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString());
            return true;
        }

        public int Insert(SqlConnection conn, AttendHolidayData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Attend_Holiday] ");
            builder.Append("( ");
            builder.Append("\t[BeginDate], ");
            builder.Append("\t[EndDate] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@BeginDate, ");
            builder.Append("\t@EndDate ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            commandParameters[0].Value = data.BeginDate;
            commandParameters[1] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            commandParameters[1].Value = data.EndDate;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public bool IsHoliday(DateTime today)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF EXISTS(SELECT [HolidayId] FROM [dbo].[Attend_Holiday] WHERE [BeginDate] <= @Today AND @Today <= [EndDate]) ");
            builder.Append("\tSELECT 1 ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT 0 ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@Today", SqlDbType.DateTime) };
            commandParameters[0].Value = Convert.ToDateTime(today.ToString("d"));
            SqlConnection connection = new SqlConnection(ConfigManager.ConnectionString);
            object obj2 = SqlHelper.ExecuteScalar(connection, CommandType.Text, builder.ToString(), commandParameters);
            connection.Close();
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[HolidayId], ");
            builder.Append("\t[BeginDate], ");
            builder.Append("\t[EndDate] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Holiday] ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Attend_Holiday";
            return set.Tables[0];
        }

        public DataTable SelectByHolidayId(SqlConnection conn, int holidayId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[HolidayId], ");
            builder.Append("\t[BeginDate], ");
            builder.Append("\t[EndDate] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Holiday] ");
            builder.Append("WHERE ");
            builder.Append("\t[HolidayId] = @HolidayId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@HolidayId", SqlDbType.Int) };
            commandParameters[0].Value = holidayId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Holiday";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, AttendHolidayData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Holiday] SET ");
            builder.Append("\t[BeginDate] = @BeginDate, ");
            builder.Append("\t[EndDate] = @EndDate ");
            builder.Append("WHERE ");
            builder.Append("\t[HolidayId] = @HolidayId ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@HolidayId", SqlDbType.Int);
            commandParameters[0].Value = data.HolidayId;
            commandParameters[1] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            commandParameters[1].Value = data.BeginDate;
            commandParameters[2] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            commandParameters[2].Value = data.EndDate;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

